package cn.haoma.base.sql;

import android.database.Cursor;

import java.lang.reflect.Field;
import java.util.HashMap;
import java.util.Set;

import cn.haoma.base.utils.L;


/**
 * 表明创建规则：例如com.college.student -> com_college_student
 * 列名创建规则：Student.name -> _name;
 */
public class SqlUtil {
    final private static HashMap<Class, String> sqlCreateMap;
    final private static HashMap<Class, HashMap<Field, Integer>> columMap;

    static {
        sqlCreateMap = new HashMap<>();
        columMap = new HashMap<>();
    }

    final public static <T> T getInstance(Cursor cursor, Class<T> c) throws IllegalAccessException, InstantiationException {
        T instance = c.newInstance();
        HashMap<Field, Integer> columIndexMap = columMap.get(c);
        if (null == columIndexMap) {
            Field[] fields = c.getDeclaredFields();
            columIndexMap = new HashMap<Field, Integer>();
            for (Field field : fields) {
                SqlField sf = field.getAnnotation(SqlField.class);
                if (null == sf || sf.type() != SqlField.SqlType.IGNORE) {
                    int column = cursor.getColumnIndex(SqlUtil.getColumnName(field));
                    if (column != -1) {
                        columIndexMap.put(field, cursor.getColumnIndex(SqlUtil.getColumnName(field)));
                    }
                }
            }
            columMap.put(c, columIndexMap);
        }
        Set<Field> fields = columIndexMap.keySet();
        for (Field field : fields) {
            Class<?> type = field.getType();
            Object value = null;
            int column = columIndexMap.get(field);
            if (type == int.class || type == Integer.class) {
                value = cursor.getInt(column);
            } else if (type == long.class || type == Long.class) {
                value = cursor.getLong(column);
            } else if (type == char.class || type == Character.class) {
                value = (char) cursor.getInt(column);
            } else if (type == byte.class || type == Byte.class) {
                value = (byte) cursor.getInt(column);
            } else if (type == boolean.class || type == Boolean.class) {
                value = "true".equals(cursor.getString(column));
            } else if (type == double.class || type == Double.class) {
                value = cursor.getDouble(column);
            } else if (type == float.class || type == Float.class) {
                value = (float) cursor.getDouble(column);
            } else if (type == short.class || type == Short.class) {
                value = cursor.getShort(column);
            } else if (type == String.class) {
                value = cursor.getString(column);
            }
            field.setAccessible(true);
            field.set(instance, value);
        }
        return instance;
    }

    final private static String getColumnName(Field field) {
        return "_" + field.getName();
    }

    /**
     * @param c
     * @return the sql for creatint table
     */
    final public static String getSqlForCreateTable(Class<?> c) {
        String sql = sqlCreateMap.get(c);
        if (null == sql) {
            StringBuilder sb = new StringBuilder();
            sb.append("CREATE TABLE IF NOT EXISTS ").append(getTableName(c)).append('(');
            Field[] fields = c.getDeclaredFields();
            for (Field field : fields) {
                SqlField sm = field.getAnnotation(SqlField.class);
                if (null != sm && sm.type() == SqlField.SqlType.IGNORE) {
                    continue;
                } else {
                    sb.append(getColumnName(field));
                    sb.append(' ');
                    Class<?> type = field.getType();
                    if (isIntegerType(type)) {
                        sb.append(" INTEGER ");
                    } else if (isFloatType(type)) {
                        sb.append(" REAL ");
                    } else {
                        sb.append(" TEXT ");
                    }
                    if (null != sm) {
                        if (sm.type() == SqlField.SqlType.AUTO_INCREMENT_KEY) {
                            sb.append("PRIMARY KEY AUTOINCREMENT");
                        } else if (sm.type() == SqlField.SqlType.PRIMARY_KEY) {
                            sb.append("PRIMARY KEY");
                        }
                    }

                    sb.append(',');
                }
            }
            sb.setCharAt(sb.length() - 1, ')');
            sql = sb.toString();
        }
        return sql;
    }

    final public static String getTableName(Class<?> c) {
        return c.getName().replace('.', '_');
    }

    /**
     * @param type
     * @return 是否是整数类型
     */
    final private static boolean isIntegerType(Class<?> type) {
        return type == int.class || type == Integer.class || type == long.class || type == Long.class || type == byte.class || type == Byte.class || type == short.class || type == Short.class || type == char.class || type == Character.class;
    }

    /**
     * @param type
     * @return 是否是整数浮点型
     */
    final private static boolean isFloatType(Class<?> type) {
        return type == float.class || type == Float.class || type == double.class || type == Double.class;
    }

    final public static String getSqlForDelete(Object obj) throws SqlException {
        String sql = "";
        //		try {
        Class c = obj.getClass();
        Field primaryKeyField = getPrimaryKeyField(c);
        primaryKeyField.setAccessible(true);
        if (null != primaryKeyField) {
            StringBuilder sb = new StringBuilder();
            String tableName = getTableName(c);
            sb.append("delete from ");
            sb.append(tableName);
            sb.append(" where ");
            sb.append(getColumnName(primaryKeyField));
            sb.append(" = ");
            Object key = null;
            try {
                key = primaryKeyField.get(obj);
            } catch (IllegalAccessException e) {
                e.printStackTrace();
            }
            if (null == key) {
                throw new SqlException("PrimaryKeyValueIsNull");
            } else {
                sb.append(String.valueOf(key));
            }
            sql = sb.toString();
        } else {
            throw new SqlException("PrimaryKeyAnoationNotFound");
        }
        return sql;
    }

    final private static Field getPrimaryKeyField(Class<?> c) {
        Field[] fields = c.getDeclaredFields();
        for (Field field : fields) {
            SqlField sf = field.getAnnotation(SqlField.class);
            if (null != sf && (sf.type() == SqlField.SqlType.PRIMARY_KEY || sf.type() == SqlField.SqlType.AUTO_INCREMENT_KEY)) {
                return field;
            }
        }
        return null;
    }

    final public static String getSqlForDeleteByCondition(Class c, String condition) throws SqlException {
        String sql = "";
        StringBuilder sb = new StringBuilder();
        String tableName = getTableName(c);
        sb.append("delete from ");
        sb.append(tableName);
        sb.append(' ');
        sb.append(condition);
        sql = sb.toString();
        return sql;
    }

    /**
     * @return a sql for insert a piece of data item
     */
    final public static String getSqlForInsert(Object obj) {
        StringBuilder sb = new StringBuilder();
        try {
            StringBuilder sbValues = new StringBuilder();
            String tableName = obj.getClass().getName().replace('.', '_');
            sb.append("INSERT INTO ").append(tableName).append('(');
            Field[] fields = obj.getClass().getDeclaredFields();
            for (Field field : fields) {
                SqlField sm = field.getAnnotation(SqlField.class);
                if (null != sm && sm.type() == SqlField.SqlType.IGNORE) {
                    continue;
                } else {
                    field.setAccessible(true);
                    Object value = field.get(obj);
                    if (null != value) {
                        if (null != sm && sm.type() == SqlField.SqlType.AUTO_INCREMENT_KEY) {
                            if ((Integer) value < 1) {
                                continue;
                            }
                        }
                        sb.append(getColumnName(field)).append(',');
                        Class<?> type = field.getType();
                        if (isIntegerType(type)) {
                            if (type == char.class || type == Character.class) {
                                char c = (char) value;
                                sbValues.append((int) c);
                            } else {
                                sbValues.append(value);
                            }
                        } else if (isFloatType(type)) {
                            sbValues.append(value);
                        } else {
                            sbValues.append('\'');
                            sbValues.append(value);
                            sbValues.append('\'');
                        }

                        sbValues.append(',');
                    }
                }
            }
            sb.setCharAt(sb.length() - 1, ')');
            sbValues.setCharAt(sbValues.length() - 1, ')');
            sb.append(" values (");
            sb.append(sbValues);
        } catch (Exception e) {
            L.e(e);
        }
        return sb.toString();
    }

    /**
     * @return a sql for replace a piece of data item
     */
    final public static String getSqlForReplace(Object obj) {
        StringBuilder sb = new StringBuilder();
        try {
            StringBuilder sbValues = new StringBuilder();
            String tableName = obj.getClass().getName().replace('.', '_');
            sb.append("REPLACE INTO ").append(tableName).append('(');
            Field[] fields = obj.getClass().getDeclaredFields();
            for (Field field : fields) {
                SqlField sm = field.getAnnotation(SqlField.class);
                if (null != sm && sm.type() == SqlField.SqlType.IGNORE) {
                    continue;
                } else {
                    field.setAccessible(true);
                    Object value = field.get(obj);
                    if (null != value) {
                        if (null != sm && sm.type() == SqlField.SqlType.AUTO_INCREMENT_KEY) {
                            if ((Integer) value < 1) {
                                continue;
                            }
                        }
                        sb.append(getColumnName(field)).append(',');
                        Class<?> type = field.getType();
                        if (isIntegerType(type)) {
                            if (type == char.class || type == Character.class) {
                                char c = (char) value;
                                sbValues.append((int) c);
                            } else {
                                sbValues.append(value);
                            }
                        } else if (isFloatType(type)) {
                            sbValues.append(value);
                        } else {
                            sbValues.append('\'');
                            sbValues.append(value);
                            sbValues.append('\'');
                        }

                        sbValues.append(',');
                    }
                }
            }
            sb.setCharAt(sb.length() - 1, ')');
            sbValues.setCharAt(sbValues.length() - 1, ')');
            sb.append(" values (");
            sb.append(sbValues);
        } catch (Exception e) {
            L.e(e);
        }
        return sb.toString();
    }

    final public static String getSqlForSelect(Object key, Class<?> c) {
        Field field = getPrimaryKeyField(c);
        if (null == field) {
            throw new IllegalArgumentException("No primary key field declared for the class " + c);
        } else {
            if (key instanceof String) {
                return "select * from " + getTableName(c) + " where " + getColumnName(getPrimaryKeyField(c)) + " = '" + key + "' limit 1";
            } else {
                return "select * from " + getTableName(c) + " where " + getColumnName(getPrimaryKeyField(c)) + " = " + key + " limit 1";
            }

        }
    }

    final public static String getSqlForUpdate(Object obj) {
        try {
            Field primaryKeyField = getPrimaryKeyField(obj.getClass());
            if (null == primaryKeyField) {
                //				Log.e("No primary key anonation declared");
                return "";
            }
            StringBuilder sb = new StringBuilder();
            StringBuilder sbValues = new StringBuilder();
            String tableName = obj.getClass().getName().replace('.', '_');
            sb.append("update ");
            sb.append(tableName);
            sb.append(" set values (");
            Field[] fields = obj.getClass().getDeclaredFields();
            for (Field field : fields) {
                SqlField sm = field.getAnnotation(SqlField.class);
                if (null == sm) {
                    continue;
                } else {
                    Object value = field.get(obj);
                    if (null != value) {
                        String vs = String.valueOf(value);
                        sb.append(getColumnName(field));
                        sb.append(',');
                        sbValues.append('\'');
                        sbValues.append(vs);
                        sbValues.append('\'');
                        sbValues.append(',');
                    }
                }
            }
            sb.setCharAt(sb.length() - 1, ')');
            sbValues.setCharAt(sbValues.length() - 1, ')');
            sb.append(" values (");
            sb.append(sbValues);
            sb.append(" where ");
            sb.append(primaryKeyField.getName());
            sb.append('=');
            sb.append(primaryKeyField.get(obj));
            System.out.println(sb.toString());
            return sb.toString();
        } catch (Exception e) {
            //			Log.exception(e);
        }
        return null;
    }

    final public static String getSqlForUpdate(Object obj, String condition) {
        try {
            StringBuilder sb = new StringBuilder();
            StringBuilder sbValues = new StringBuilder();
            String tableName = obj.getClass().getName().replace('.', '_');
            sb.append("update ");
            sb.append(tableName);
            sb.append(" set values (");
            Field[] fields = obj.getClass().getDeclaredFields();
            for (Field field : fields) {
                SqlField sm = field.getAnnotation(SqlField.class);
                if (null == sm) {
                    continue;
                } else {
                    Object value = field.get(obj);
                    if (null != value) {
                        String vs = String.valueOf(value);
                        sb.append(getColumnName(field));
                        sb.append(',');
                        sbValues.append('\'');
                        sbValues.append(vs);
                        sbValues.append('\'');
                        sbValues.append(',');
                    }
                }
            }
            sb.setCharAt(sb.length() - 1, ')');
            sbValues.setCharAt(sbValues.length() - 1, ')');
            sb.append(" values (");
            sb.append(sbValues);
            sb.append(' ');
            sb.append(condition);
            sb.append(" where ");
            return sb.toString();
        } catch (Exception e) {
            //			Log.exception(e);
        }
        return null;
    }

}
